<?php


namespace App\Listeners;


use App\Models\ExcelFile;
use App\Models\InterestRecord;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class RecordUtils
{
    public static function getGYRecode($status, $where, $excel_record_id, $branch_list)
    {
        try {
            $interest_records = InterestRecord::whereIn('status', $status)->whereIn('branch_id', $branch_list)->where($where)->with('order.product')->with('order.user')->with('order.bank_card')->with('order.worker')->get();
            $spreadsheet = new Spreadsheet();
            $orderSheet = $spreadsheet->getActiveSheet();
            // 设置工作表名称
            $orderSheet->setTitle('打款记录表');
            // 设置表头
            $orderSheet->setCellValueByColumnAndRow(1, 1, '合同编号');      // A
            $orderSheet->setCellValueByColumnAndRow(2, 1, '客户姓名');       // B
            $orderSheet->setCellValueByColumnAndRow(3, 1, '起收益日期');       // C
            $orderSheet->setCellValueByColumnAndRow(4, 1, '合同金额');     // D
            $orderSheet->setCellValueByColumnAndRow(5, 1, '年化收益率(%)');     // E
            $orderSheet->setCellValueByColumnAndRow(6, 1, '本次汇款收益');     // F
            $orderSheet->setCellValueByColumnAndRow(7, 1, '每月汇款');     // G
            $orderSheet->setCellValueByColumnAndRow(8, 1, '汇款周期(每次)');     // H
            $orderSheet->setCellValueByColumnAndRow(9, 1, '本次汇款本金');     // I
            $orderSheet->setCellValueByColumnAndRow(10, 1, '本次汇款');     // J
            $orderSheet->setCellValueByColumnAndRow(11, 1, '本次汇款(取整)');     // K
            $orderSheet->setCellValueByColumnAndRow(12, 1, '每月汇款(取整)');      // L
            $orderSheet->setCellValueByColumnAndRow(13, 1, '汇款日期');      // M
            $orderSheet->setCellValueByColumnAndRow(14, 1, '实际汇款日期');      // M
            $orderSheet->setCellValueByColumnAndRow(15, 1, '签署日期');      // N
            $orderSheet->setCellValueByColumnAndRow(16, 1, '开户行');      // O
            $orderSheet->setCellValueByColumnAndRow(17, 1, '银行账号');      // P
            $orderSheet->setCellValueByColumnAndRow(18, 1, '合同到期日期');      // Q
            $orderSheet->setCellValueByColumnAndRow(19, 1, '经办人');     // R
            $orderSheet->setCellValueByColumnAndRow(20, 1, '经办人电话');      // S
            $orderSheet->setCellValueByColumnAndRow(21, 1, '产品名称');     // T
            $orderSheet->setCellValueByColumnAndRow(22, 1, '产品周期');       // U
            $orderSheet->setCellValueByColumnAndRow(23, 1, '周期单位');     // V
            $row = 2;
            foreach ($interest_records as $record) {
                if ($row > 20000) {
                    break;
                }
                $order_on = $record['order']['contract_no'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(1, $row, $order_on);      // A
                $user_name = $record['order']['user']['name'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(2, $row, $user_name);       // B
                $valid_time = $record['order']['valid_time'];  // 起收益日期
                $valid_year = (int)(date('Y', strtotime($valid_time)));
                $valid_month = (int)(date('m', strtotime($valid_time)));
                $valid_day = (int)(date('d', strtotime($valid_time)));
                $orderSheet->setCellValueByColumnAndRow(3, $row, $valid_year . '/' . $valid_month . '/' . $valid_day);       // C
                $order_money = $record['order']['order_money'];      // 投资金额（元）
                $orderSheet->setCellValueByColumnAndRow(4, $row, $order_money);     // D
                $rate = $record['order']['product']['rate'];             // 年化收益率利率
                $orderSheet->setCellValueByColumnAndRow(5, $row, $rate);     // E
                $rate_money = round($record['interest'], 2);
                $orderSheet->setCellValueByColumnAndRow(6, $row, $rate_money);     // F
                $month_money = $order_money * $rate / 1200;
                $orderSheet->setCellValueByColumnAndRow(7, $row, $month_money);     // G
                $pay_long = $record['order']['product']['pay_long'] . '/月';
                $orderSheet->setCellValueByColumnAndRow(8, $row, $pay_long);     // H
                $money = $record['money'];
                $orderSheet->setCellValueByColumnAndRow(9, $row, $money);     // I
                $this_money = $money + $rate_money;
                $orderSheet->setCellValueByColumnAndRow(10, $row, $this_money);     // J
                $this_money_round = round($this_money, 0);
                $orderSheet->setCellValueByColumnAndRow(11, $row, $this_money_round);     // K
                $month_money_round = round($month_money, 0);
                $orderSheet->setCellValueByColumnAndRow(12, $row, $month_money_round);      // L
                $orderSheet->setCellValueByColumnAndRow(13, $row, date('Y/m/d', strtotime($record['date'])));      // M
                $real_time = date('Y/m/d', strtotime($record['real_time']));
                if ($real_time != '1970-01-01') {
                    $real_year = (int)(date('Y', strtotime($real_time)));
                    $real_month = (int)(date('m', strtotime($real_time)));
                    $real_day = (int)(date('d', strtotime($real_time)));
                    $orderSheet->setCellValueByColumnAndRow(14, $row, $real_year . '/' . $real_month . '/' . $real_day);      // N
                }
                $sign_time = $record['order']['sign_time'];  // 签署日期
                $sign_year = (int)(date('Y', strtotime($sign_time)));
                $sign_month = (int)(date('m', strtotime($sign_time)));
                $sign_day = (int)(date('d', strtotime($sign_time)));
                $orderSheet->setCellValueByColumnAndRow(15, $row, date('Y/m/d', strtotime($sign_year . '/' . $sign_month . '/' . $sign_day)));      // O
                $bank_card = $record['order']['bank_card']['bank_num'] ?? '';      // 回款银行卡号
                $bank_card_open = $record['order']['bank_card']['bank_sum_name'] ?? '';            // 开户行名称
                $orderSheet->setCellValueByColumnAndRow(16, $row, $bank_card_open);      // P
                $orderSheet->setCellValueByColumnAndRow(17, $row, $bank_card . "\t");      // Q
                $contract_end_date = $record['order']['contract_end_date'] ?? '';   // 到期时间
                $orderSheet->setCellValueByColumnAndRow(18, $row, date('Y/m/d', strtotime($contract_end_date)));      // R
                $worker_name = $record['order']['worker']['name'] ?? '';
                $worker_num = $record['order']['worker']['contactNum'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(19, $row, $worker_name);     // S
                $orderSheet->setCellValueByColumnAndRow(20, $row, $worker_num . "\t");      // T
                $product_name = $record['order']['product']['name'] ?? '';        // 产品名称
                $orderSheet->setCellValueByColumnAndRow(21, $row, $product_name);     // U
                $long_time = $record['order']['product']['long_time'];       // 产品周期
                $orderSheet->setCellValueByColumnAndRow(22, $row, $long_time);       // V
                $long_time_unit = $record['order']['product']['time_unit'];
                if ($long_time_unit == 1) {
                    $orderSheet->setCellValueByColumnAndRow(23, $row, '日');     // W
                } elseif ($long_time_unit == 2) {
                    $orderSheet->setCellValueByColumnAndRow(23, $row, '月');     // W
                } elseif ($long_time_unit == 3) {
                    $orderSheet->setCellValueByColumnAndRow(23, $row, '年');     // W
                }
                $row = $row + 1;
            }
            $filename = 'D' . time() . '.xlsx';
            $writer = new Xlsx($spreadsheet);
            $path = public_path('excel/' . date("Ymd"));
            File::makeDirectory($path, 0777, true, true);
            $writer->save('../public/excel/' . date("Ymd") . '/' . $filename);
            unset($spreadsheet);
            $exists = Storage::disk('new_ftp')->put('htdocs/excel_files/' . date("Ymd") . '/' . $filename, file_get_contents('../public/excel/' . date("Ymd") . '/' . $filename));
            $file_url = env("FTP2_URL") . 'excel_files/' . date("Ymd") . '/' . $filename;
            $file_path = 'excel_files/' . date("Ymd") . '/' . $filename;
            if ($exists) {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 1;
                $excel_records->file_url = $file_url;
                $excel_records->file_path = $file_path;
                $excel_records->save();
            } else {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 2;
                $excel_records->save();
            }
        } catch (\Throwable $th) {
            $excel_records = ExcelFile::find($excel_record_id);
            $excel_records->is_success = 2;
            $excel_records->save();
        }
    }

    public static function getOtherRecode($status, $where, $excel_record_id, $branch_list)
    {
        try {
            $interest_records = InterestRecord::whereIn('status', $status)->whereIn('branch_id', $branch_list)->where($where)->with('branch')->with('order.product')->with('order.user')->with('order.bank_card.bank')->with('order.worker')->get();
            $now_date = date('Y-m-d', time());
            $spreadsheet = new Spreadsheet();
            $orderSheet = $spreadsheet->getActiveSheet();
            // 设置工作表名称
            $orderSheet->setTitle('打款记录表');
            // 设置表头
            $orderSheet->setCellValueByColumnAndRow(1, 1, '应付款日期');      // A
            $orderSheet->setCellValueByColumnAndRow(2, 1, '实际付款金额');       // B
            $orderSheet->setCellValueByColumnAndRow(3, 1, '应付本金');       // B
            $orderSheet->setCellValueByColumnAndRow(4, 1, '应付利息');     // C
            $orderSheet->setCellValueByColumnAndRow(5, 1, '利息取整');     // C
            $orderSheet->setCellValueByColumnAndRow(6, 1, '应付合计');     // D
            $orderSheet->setCellValueByColumnAndRow(7, 1, '应付取整');     // E
            $orderSheet->setCellValueByColumnAndRow(8, 1, '投资日期');     // C
            $orderSheet->setCellValueByColumnAndRow(9, 1, '合同号');     // D
            $orderSheet->setCellValueByColumnAndRow(10, 1, '系统订单号');     // E
            $orderSheet->setCellValueByColumnAndRow(11, 1, '投资人名称');     // E
            $orderSheet->setCellValueByColumnAndRow(12, 1, '投资人联系方式');      // F
            $orderSheet->setCellValueByColumnAndRow(13, 1, '订单金额');      // G
            $orderSheet->setCellValueByColumnAndRow(14, 1, '标准金额');      // G
            $orderSheet->setCellValueByColumnAndRow(15, 1, '投资期限');      // H
            $orderSheet->setCellValueByColumnAndRow(16, 1, '投资方式');      // I
            $orderSheet->setCellValueByColumnAndRow(17, 1, '月付');      // J
            $orderSheet->setCellValueByColumnAndRow(18, 1, '利率(年化)');     // K
            $orderSheet->setCellValueByColumnAndRow(19, 1, '存量/转存/新单');      // L
            $orderSheet->setCellValueByColumnAndRow(20, 1, '到期日期');     // M
            $orderSheet->setCellValueByColumnAndRow(21, 1, '投资人身份证号码');       // N
            $orderSheet->setCellValueByColumnAndRow(22, 1, '回款银行账号');     // O
            $orderSheet->setCellValueByColumnAndRow(23, 1, '回款银行开户行');          // P
            $orderSheet->setCellValueByColumnAndRow(24, 1, '回款银行名称');     // Q
            $orderSheet->setCellValueByColumnAndRow(25, 1, '理财经理');     // Q
            $orderSheet->setCellValueByColumnAndRow(26, 1, '团队经理');           // R
            $orderSheet->setCellValueByColumnAndRow(27, 1, '营业部部经理');      // S
            $orderSheet->setCellValueByColumnAndRow(28, 1, '分公司经理');         // T
            $orderSheet->setCellValueByColumnAndRow(29, 1, '城市大区经理');         // U
            $orderSheet->setCellValueByColumnAndRow(30, 1, '副总裁');      // V
            $orderSheet->setCellValueByColumnAndRow(31, 1, '总裁');           // W
            $orderSheet->setCellValueByColumnAndRow(32, 1, '代理公司/分公司');       // X
            $orderSheet->setCellValueByColumnAndRow(33, 1, '是否在职');                 // Y
            $orderSheet->setCellValueByColumnAndRow(34, 1, '备注');                 // Y
            $row = 2;
            foreach ($interest_records as $record) {
                if ($row > 20000) {
                    break;
                }
                if ($record['date']) {
                    $orderSheet->setCellValueByColumnAndRow(1, $row, date('Y-m-d', strtotime($record['date'])));      // A
                }
                $orderSheet->setCellValueByColumnAndRow(2, $row, $record['interest_money'] ?? 0);       // B
                $orderSheet->setCellValueByColumnAndRow(3, $row, $record['money'] ?? 0);       // B
                $orderSheet->setCellValueByColumnAndRow(4, $row, $record['interest'] ?? 0);     // C
                if ($record['interest']) {
                    $orderSheet->setCellValueByColumnAndRow(5, $row, ceil($record['interest']));     // C
                } else {
                    $orderSheet->setCellValueByColumnAndRow(5, $row, 0);     // C
                }
                $all_money = $record['money'] + $record['interest'];
                $orderSheet->setCellValueByColumnAndRow(6, $row, $all_money);     // D
                $orderSheet->setCellValueByColumnAndRow(7, $row, $record['payment_amount'] ?? 0);     // E
                if ($record['order']['sign_time']) {
                    $orderSheet->setCellValueByColumnAndRow(8, $row, date('Y-m-d', strtotime($record['order']['sign_time'])));     // C
                }
                $orderSheet->setCellValueByColumnAndRow(9, $row, $record['order']['contract_no'] ?? '');     // D
                $order_on = $record['order']['order_no'];
                if ($order_on) {
                    $orderSheet->setCellValueByColumnAndRow(10, $row, $order_on . "\t");     // E
                }
                $orderSheet->setCellValueByColumnAndRow(11, $row, $record['order']['user']['name']);     // E
                $contact_manner = $record['order']['user']['contact_manner'] ?? '';
                if ($contact_manner) {
                    $orderSheet->setCellValueByColumnAndRow(12, $row, $contact_manner . "\t");      // F
                }
                $orderSheet->setCellValueByColumnAndRow(13, $row, $record['order']['order_money']);      // G
                $orderSheet->setCellValueByColumnAndRow(14, $row, round($record['order']['results_money'], 2));      // G
                $orderSheet->setCellValueByColumnAndRow(15, $row, $record['order']['product']['long_time']);      // H
                // $investment_way = $record['order']['investment_way'] ?? '';        // 投资方式
                $investment_way = 1;        // 投资状态  1常规  2通联待付款 3通联已付款
                if ($investment_way == 1) {
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '常规');
                } elseif ($investment_way == 2) {
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '通联待付款');
                } elseif ($investment_way == 3) {
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '通联已付款');
                }
                $pay_types = $record['order']['product']['pay_type'] ?? '';                      // 结算方式
                if ($pay_types == 4) {
                    if ($record['order']['contract_end_date'] > $now_date) {
                        $pay_type = '一次性付';
                    } else {
                        $pay_type = '一次性付已到期';
                    }
                } else {
                    if ($record['order']['contract_end_date'] > $now_date) {
                        $pay_long = $record['order']['product']['pay_long'];
                        switch ($pay_long) {
                            case 1:
                                $pay_type = '月付';
                                break;
                            case 3:
                                $pay_type = '季付';
                                break;
                            case 12:
                                $pay_type = '年付';
                                break;
                            default:
                                $pay_type = '一次性付';
                        }
                    } else {
                        $pay_long = $record['order']['product']['pay_long'];
                        switch ($pay_long) {
                            case 1:
                                $pay_type = '月付已到期';
                                break;
                            case 3:
                                $pay_type = '季付已到期';
                                break;
                            case 12:
                                $pay_type = '年付已到期';
                                break;
                            default:
                                $pay_type = '一次性付已到期';
                        }
                    }
                }
                $orderSheet->setCellValueByColumnAndRow(17, $row, $pay_type);      // J
                $rate = $record['order']['product']['rate'] . '%';             // 利率
                $orderSheet->setCellValueByColumnAndRow(18, $row, $rate);     // K
                switch ($record['order']['is_first']) {
                    case 1:
                        $is_first = '新单';
                        break;
                    case 2:
                        $is_first = '存量';
                        break;
                    case 3:
                        $is_first = '转续';
                        break;
                    default:
                        $is_first = '';
                }                       // 订单类型
                $orderSheet->setCellValueByColumnAndRow(19, $row, $is_first);      // L
                if ($record['order']['contract_end_date']) {
                    $orderSheet->setCellValueByColumnAndRow(20, $row, date('Y-m-d', strtotime($record['order']['contract_end_date'])));     // M
                }
                $id_num = $record['order']['user']['id_number'] ?? '';
                if ($id_num) {
                    $orderSheet->setCellValueByColumnAndRow(21, $row, $id_num . "\t");
                }
                $bank_card_id = $record['order']['bank_card_id'] ?? '';
                if ($bank_card_id) {
                    $bank_card = $record['order']['bank_card']['bank_num'];
                    $bank_sum_name = $record['order']['bank_card']['bank_sum_name'];
                    $bank_name = $record['order']['bank_card']['bank']['bank_name'];
                    $orderSheet->setCellValueByColumnAndRow(22, $row, $bank_card . "\t");     // O
                    $orderSheet->setCellValueByColumnAndRow(23, $row, $bank_sum_name);          // P
                    $orderSheet->setCellValueByColumnAndRow(24, $row, $bank_name);          // P
                }
                $manager_list = [];
                if ($record['order']['wealth_manage_id']) {
                    $magager_dic['worker'] = $record['order']['wealth_manage_id'];
                    $magager_dic['col'] = 25;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['group_manage_id']) {
                    $magager_dic['worker'] = $record['order']['group_manage_id'];
                    $magager_dic['col'] = 26;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['sales_manage_id']) {
                    $magager_dic['worker'] = $record['order']['sales_manage_id'];
                    $magager_dic['col'] = 27;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['department_manage_id']) {
                    $magager_dic['worker'] = $record['order']['department_manage_id'];
                    $magager_dic['col'] = 28;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['regional_manage_id']) {
                    $magager_dic['worker'] = $record['order']['regional_manage_id'];
                    $magager_dic['col'] = 29;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['vice_president_id']) {
                    $magager_dic['worker'] = $record['order']['vice_president_id'];
                    $magager_dic['col'] = 30;
                    $manager_list[] = $magager_dic;
                }
                if ($record['order']['president_id']) {
                    $magager_dic['worker'] = $record['order']['president_id'];
                    $magager_dic['col'] = 31;
                    $manager_list[] = $magager_dic;
                }
                foreach ($manager_list as $manager) {
                    $orderSheet->setCellValueByColumnAndRow($manager['col'], $row, $manager['worker']);
                }
                $branch = $record['branch']['name'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(32, $row, $branch);       // X
                if (isset($interest_records['order']['worker'])) {
                    $worker_status = $interest_records['order']['worker']['user_status'] == 1 ? '在职' : '离职';
                    $orderSheet->setCellValueByColumnAndRow(33, $row, $worker_status);                 // Y
                }
                if ($record['order']['remark']) {
                    $orderSheet->setCellValueByColumnAndRow(34, $row, $record['order']['remark']);                 // Y
                }
                $row = $row + 1;
            }
            $filename = 'D' . time() . '.xlsx';
            $writer = new Xlsx($spreadsheet);
            $path = public_path('excel/' . date("Ymd"));
            File::makeDirectory($path, 0777, true, true);
            $writer->save('../public/excel/' . date("Ymd") . '/' . $filename);
            unset($spreadsheet);
            $exists = Storage::disk('new_ftp')->put('htdocs/excel_files/' . date("Ymd") . '/' . $filename, file_get_contents('../public/excel/' . date("Ymd") . '/' . $filename));
            $file_url = env("FTP2_URL") . 'excel_files/' . date("Ymd") . '/' . $filename;
            $file_path = 'excel_files/' . date("Ymd") . '/' . $filename;
            if ($exists) {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 1;
                $excel_records->file_url = $file_url;
                $excel_records->file_path = $file_path;
                $excel_records->save();
            } else {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 2;
                $excel_records->save();
            }
        } catch (\Throwable $th) {
            $excel_records = ExcelFile::find($excel_record_id);
            $excel_records->is_success = 2;
            $excel_records->save();
        }
    }
}
